import pandas as pd
from datetime import datetime
import jsjj.excel2Mysql.pub as pub


def clear(conn, engine):
    return


def mainProcess(conn, engine):
    mainProcess1(conn, engine)
    mainProcess2(conn, engine)
    mainProcess3(conn, engine)

    # 初步清洗
    clear(conn, engine)
    return


def mainProcess1(conn, engine):
    filelist = pub.getRawFileList(getDirectory())

    blackList = pd.read_csv('./excludeFiles')

    # 拼多多-电霸-热销商品
    for file in filelist[0]:

        if file.find('~') != -1:
            # 临时文件不处理
            continue

        # 原始数据整理情况汇总-V8-7.8.xlsx
        # 住宅家具-三个平台类目对比-V1.xlsx
        if file.find('住宅家具-三个平台类目对比-V1.xlsx') == -1:
            # 临时文件不处理
            continue

        # 读取(淘宝)
        df = pd.read_excel(file, sheet_name=3)

        # 转换
        dfMysql = transfer1(df, file)

        # 存盘
        # 解决可能出现的超时问题bugfix 2020.08.18
        conn.connection.connection.ping(reconnect=True)
        dfMysql.to_sql(name=getTableName(), con=conn, if_exists='append', index=False)

    return


def mainProcess2(conn, engine):
    filelist = pub.getRawFileList(getDirectory())

    blackList = pd.read_csv('./excludeFiles')

    # 拼多多-电霸-热销商品
    for file in filelist[0]:

        if file.find('~') != -1:
            # 临时文件不处理
            continue

        # 原始数据整理情况汇总-V8-7.8.xlsx
        # 住宅家具-三个平台类目对比-V1.xlsx
        if file.find('住宅家具-三个平台类目对比-V1.xlsx') == -1:
            # 临时文件不处理
            continue

        # 读取(京东)
        df = pd.read_excel(file, sheet_name=4)

        # 转换
        dfMysql = transfer2(df, file)

        # 存盘
        # 解决可能出现的超时问题bugfix 2020.08.18
        conn.connection.connection.ping(reconnect=True)
        dfMysql.to_sql(name=getTableName(), con=conn, if_exists='append', index=False)

    return


def mainProcess3(conn, engine):
    filelist = pub.getRawFileList(getDirectory())

    blackList = pd.read_csv('./excludeFiles')

    # 拼多多-电霸-热销商品
    for file in filelist[0]:

        if file.find('~') != -1:
            # 临时文件不处理
            continue

        # 原始数据整理情况汇总-V8-7.8.xlsx
        # 住宅家具-三个平台类目对比-V1.xlsx
        if file.find('住宅家具-三个平台类目对比-V1.xlsx') == -1:
            # 临时文件不处理
            continue

        # 读取（拼多多）
        df = pd.read_excel(file, sheet_name=5)

        # 转换
        dfMysql = transfer3(df, file)

        # 存盘
        # 解决可能出现的超时问题bugfix 2020.08.18
        conn.connection.connection.ping(reconnect=True)
        dfMysql.to_sql(name=getTableName(), con=conn, if_exists='append', index=False)

    return


def getDirectory():
    fileDirectory = "D:\\简尚家居\\excel文件\\2020_07_08\\源数据-定义\\"
    return fileDirectory


def getTableName():
    mysqlTableName = '平台_类目表'
    # mysqlTableName = 'test'
    return mysqlTableName


def transfer1(df, fileName):
    print('本次处理文件为:' + fileName)

    columnsMysql = ['statDate', 'col1', 'col2', 'col3', 'col4', 'col5', 'col6', 'col7', 'col8', 'col9', 'col10',
                    'col11']
    dfMysql = pd.DataFrame(df)

    # 6月是不需要存储的
    # dfMysql.columns.insert(1, 'statDate')
    dfMysql.rename(
        columns={'一级类目': 'cat1', '二级类目': 'cat2', '叶子类目': 'catLeaf'}, inplace=True)
    dfMysql = dfMysql.drop(columns=['叶子类目.1'])
    dfMysql['statDate'] = datetime.now()
    dfMysql['channel'] = '淘宝'

    return dfMysql


def transfer2(df, fileName):
    print('本次处理文件为:' + fileName)

    dfMysql = pd.DataFrame(df)

    # 6月是不需要存储的
    # dfMysql.columns.insert(1, 'statDate')
    dfMysql.rename(
        columns={'一级': 'cat1', '二级': 'cat2', '三级': 'catLeaf'}, inplace=True)
    dfMysql['statDate'] = datetime.now()
    dfMysql['channel'] = '京东'

    return dfMysql


def transfer3(df, fileName):
    print('本次处理文件为:' + fileName)

    dfMysql = pd.DataFrame(df)

    # 6月是不需要存储的
    # dfMysql.columns.insert(1, 'statDate')
    dfMysql.rename(
        columns={'一级类目': 'cat1', '二级类目': 'cat2', '三级类目': 'cat3', '四级类目': 'catLeaf'}, inplace=True)
    dfMysql = dfMysql.drop(columns=['三级类目-1 '])
    dfMysql['statDate'] = datetime.now()
    dfMysql['channel'] = '拼多多'

    return dfMysql
